package milkproduction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import milkproduction.to.FinishedProductTO;
import miracom.one.MilkProduction.util.DatabaseUtility;

public class ImpregnatorDAO {
	private static ImpregnatorDAO instance = new ImpregnatorDAO();
	
	private DatabaseUtility dbUtil;
	
	public static ImpregnatorDAO getInstance(){
		return instance;
	}
	
	//시퀀스 완제품 번호 조회
	public int selectFinishedProductNo() throws SQLException{
		String sql = "select finished_product_seq.nextval from dual";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int no = 0;
		try {
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				no = rset.getInt(1);
			}
		} finally {
			dbUtil.close(rset, pstmt, conn);
		}
		return no;
	}
	
	//원유 번호 조회
	public int selectRowMilkNo() throws SQLException{
		String sql = "select row_milk_no from row_milk";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int no = 0;
		try {
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				no = rset.getInt(1);
			}
		} finally {
			dbUtil.close(rset, pstmt, conn);
		}
		return no;
	}
	
	//출고 번호 조회
	public int selectReleaseNo() throws SQLException{
		String sql = "select release_no from release";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int no = 0;
		try {
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				no = rset.getInt(1);
			}
		} finally {
			dbUtil.close(rset, pstmt, conn);
		}
		return no;
	}
	
	//용기 번호 조회
	public int selectContainerNo() throws SQLException{
		String sql = "select container_no from container";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int no = 0;
		try {
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				no = rset.getInt(1);
			}
		} finally {
			dbUtil.close(rset, pstmt, conn);
		}
		return no;
	}
	
	//완제품 저장
	public void insertFinishedProduct(FinishedProductTO fpt) throws SQLException{
		String sql = "insert into finished_product(finished_product_no, quantity, product_day, row_milk_no, container_no, release_no) values(?, ?, sysdate, ?, ?, 0)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dbUtil.getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, fpt.getFinishedProductNo());
			pstmt.setInt(2, fpt.getQuantity());
			pstmt.setInt(3, fpt.getRowMilkNo());
			pstmt.setInt(4, fpt.getContainerNo());
			pstmt.setInt(5, fpt.getReleaseNo());
			pstmt.executeUpdate();
		} finally {
			dbUtil.close(pstmt, conn);
		}
	}
}
